﻿Imports System.IO
Imports System.Data.SqlClient

Public Class CaseNotesForm

    Dim TheView As DataView

    Private Sub FontButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

        ' FontDialog.Font = NotesBox.SelectionFont

        '    If FontDialog.ShowDialog() = Windows.Forms.DialogResult.OK Then
        'NotesBox.SelectionFont = FontDialog.Font
        '    End If
    End Sub

    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()

    End Sub

    Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
        SaveLastNote()

        'before you close set the amount of attachments and notes
        With CasesForm
            With .DetailGrid

                .Item(.Columns("CaseNotes").Index, .CurrentCell.RowIndex).Value = TheRepeater.ItemCount
                .Item(.Columns("CaseAtts").Index, .CurrentCell.RowIndex).Value = AttachmentsBox.Items.Count

            End With
        End With

        Me.Close()

    End Sub

    Private Sub CaseNotesForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SplitContainer1.SplitterDistance = Me.Height - 200

        'Load the Repeater


        LoadView()
        LoadRepeater()

        LoadAttachmentsBox()


    End Sub

    Private Sub LoadView()
        Dim sql As String
        Dim DataCut_ID As String
        Dim Rid As String
        Dim ProviderName As String
        Dim CaseName As String
        Dim LA As String

        With CasesForm.DetailGrid
            Rid = .Item(.Columns("RID").Index, .CurrentRow.Index).Value.ToString
            ProviderName = .Item(.Columns("Provider").Index, .CurrentRow.Index).Value.ToString
            LA = .Item(.Columns("Local Authority").Index, .CurrentRow.Index).Value.ToString
        End With


        With CasesForm.TemplateGrid
            DataCut_ID = .Item(.Columns("DataCut_ID").Index, .CurrentRow.Index).Value.ToString
            CaseName = .Item(.Columns("Case").Index, .CurrentRow.Index).Value.ToString
        End With

        sql = "Select * from Case_Notes where DataCut_ID = '" & DataCut_ID & "' and RID = '" & Rid & "' order by TheDate"
        TheView = LoadDataView(sql)
    End Sub

    Private Sub LoadRepeater()
        Dim ProviderName As String
        Dim CaseName As String
        Dim r As Integer
        Dim LA As String

        With CasesForm.DetailGrid

            ProviderName = .Item(.Columns("Provider").Index, .CurrentRow.Index).Value.ToString
            LA = .Item(.Columns("Local Authority").Index, .CurrentRow.Index).Value.ToString
        End With


        With CasesForm.TemplateGrid

            CaseName = .Item(.Columns("Case").Index, .CurrentRow.Index).Value.ToString
        End With

        If TheView.Count <> 0 Then
            For r = 1 To TheView.Count
                TheRepeater.AddNew()

            Next
        End If

        HeaderBox.Text = CaseName & vbNewLine & ProviderName & " - " & LA

    End Sub

    Private Sub AddAttachmentButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddAttachmentButton.Click

        FileDialog.FileName = ""
        FileDialog.Title = "Add attachment"
        If FileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
            With FileDialog



                Dim DataCut_ID As String
                Dim RID As String

                With CasesForm.TemplateGrid
                    DataCut_ID = .Item(.Columns("DataCut_ID").Index, .CurrentRow.Index).Value.ToString
                End With

                With CasesForm.DetailGrid
                    RID = .Item(.Columns("RID").Index, .CurrentRow.Index).Value.ToString
                End With

                Try
                    ShowLoadingForm()

                    'SaveAttachment(DataCut_ID, RID, RemoveBadChars(.SafeFileName), .FileName)
                    InsertFile(.FileName, "uspInsertFile", DataCut_ID, RID, RemoveBadChars(.SafeFileName))

                Catch ex As Exception
                    HideLoadingForm()
                    MessageBox.Show("There has been an error, please take a screen print and send it to TheStore.EFA@education.gsi.gov.uk:  " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try

                LoadItemInBox(RemoveBadChars(.SafeFileName))
                HideLoadingForm()


            End With
        End If
    End Sub

    Private Sub LoadItemInBox(ByVal TheFileName As String)

        Dim ImageIndex As Integer

        If TheFileName.ToString.Contains(".doc") Then
            ImageIndex = 1
        Else
            If TheFileName.ToString.Contains(".xls") Then
                ImageIndex = 2
            Else
                If TheFileName.ToString.Contains(".pdf") Then
                    ImageIndex = 3
                Else
                    ImageIndex = 0
                End If
            End If
        End If


        AttachmentsBox.Items.Add(TheFileName, ImageIndex)
    End Sub

    Private Sub LoadAttachmentsBox()
        AttachmentsBox.Items.Clear()

        Dim sql As String
        Dim DataCut_ID As String
        Dim Rid As String
        Dim r As DataRowView
        Dim AView As DataView

        With CasesForm.DetailGrid
            Rid = .Item(.Columns("RID").Index, .CurrentRow.Index).Value.ToString

        End With

        With CasesForm.TemplateGrid
            DataCut_ID = .Item(.Columns("DataCut_ID").Index, .CurrentRow.Index).Value.ToString

        End With

        sql = "Select * from Case_Attachments where DataCut_ID = '" & DataCut_ID & "' and RID = '" & Rid & "'"
        AView = LoadDataView(sql)

        For Each r In AView
            LoadItemInBox(r.Item("FileName"))
        Next
    End Sub


    Public Function InsertFile(ByRef FileName As String, ByRef storedProc As String, ByVal DataCut_ID As String, ByVal RID As String, ByVal SafeFileName As String) As Boolean
        Try

            'FileInfo instance so we can get all the
            'information we need regarding the image
            Dim fInfo As New FileInfo(FileName)

            'Get the length of the image for the byte array
            'we create later in the function		
            Dim len As Long = fInfo.Length

            'This is the connections tring connecting to your database
            Dim connString As String = StoreConnect

            'Open a FileStream the length of the image being inserted
            Using stream As New FileStream(FileName.Trim(), FileMode.Open)
                'Create a new byte array the size of the length of the file
                Dim imgData() As Byte = New Byte(Convert.ToInt32(len - 1)) {}

                'Read the byte array into the buffer
                stream.Read(imgData, 0, len)

                'Create our Sql connection
                Using con As New SqlConnection(connString)

                    'Create the command object that will do
                    'the database work
                    Using cmd As New SqlCommand()
                        con.Open()

                        'Set the properties of ouor connection object
                        cmd.Connection = con
                        cmd.CommandType = CommandType.StoredProcedure
                        cmd.CommandText = storedProc

                        'Add the parameters for the stored procedure
                        cmd.Parameters.AddWithValue("@DataCut_ID", DataCut_ID)
                        cmd.Parameters.AddWithValue("@RID", RID)
                        cmd.Parameters.AddWithValue("@FileName", SafeFileName)
                        cmd.Parameters.AddWithValue("@User_Name", CurrentUser.Name)
                        cmd.Parameters.AddWithValue("@Attachment", imgData)


                        'Execute the Stored Procedure
                        cmd.ExecuteNonQuery()

                        Return True
                    End Using
                End Using
            End Using
        Catch ex As SqlException
            MessageBox.Show(ex.ToString)
            Return False
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            Return False
        End Try
    End Function



    Private Sub SaveAttachment(ByVal DataCutID As String, ByVal RID As String, ByVal SafeFileName As String, ByVal TheFileName As String)
        Dim strConn As String = StoreConnect
        Dim NewString As String

        NewString = strConn

        ' NewString = NewString.Substring(0, Len(NewString) - 4)
        'NewString = NewString & "SSPI"

        Dim da As New OleDb.OleDbDataAdapter("select * from Case_Attachments", "Provider=SQLOLEDB;" & NewString)
        Dim ds As New DataSet()
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim row As DataRow

        '    da.MissingSchemaAction = MissingSchemaAction.AddWithKey

        Dim arr() As Byte
        'Resize array so that it can accomodate the file
        ReDim arr(FileLen(TheFileName) - 1)
        FileOpen(1, TheFileName, OpenMode.Binary, OpenAccess.Read, OpenShare.Shared)
        FileGet(1, arr)
        FileClose(1)

        da.Fill(ds, "Case_Attachments")
        'now save it
        row = ds.Tables(0).NewRow
        row.Item("DataCut_ID") = DataCutID
        row.Item("RID") = RID
        row.Item("FileName") = SafeFileName
        row.Item("User_Name") = CurrentUser.Name
        row.Item("Attachment") = arr
        ds.Tables(0).Rows.Add(row)
        da.Update(ds, "Case_Attachments")

    End Sub

    Private Sub SaveLastNote()
        If TheRepeater.ItemCount = 0 Then Exit Sub
        'only save it if it belongs to the current user

        Dim rpt As Microsoft.VisualBasic.PowerPacks.DataRepeaterItem

        For Each rpt In TheRepeater.Controls
            If rpt.ItemIndex = TheRepeater.ItemCount - 1 Then
                Dim TheNote As RichTextBox = rpt.Controls("NotesBox")

                If TheNote.Text = "" Then
                    Exit Sub
                End If


                If TheView(TheRepeater.ItemCount - 1).Item("User_Name").ToString <> CurrentUser.Name Then Exit Sub

                'save it
                Dim sql As String
                Dim DataCut_ID As String
                Dim Rid As String


                With CasesForm.TemplateGrid
                    DataCut_ID = .Item(.Columns("DataCut_ID").Index, .CurrentRow.Index).Value.ToString
                End With

                With CasesForm.DetailGrid
                    Rid = .Item(.Columns("RID").Index, .CurrentRow.Index).Value.ToString
                End With



                sql = "update Case_Notes set Note = '" & RemoveBadChars(TheNote.Text) & "' where DataCut_ID = '" & DataCut_ID & "' and User_Name = '" & CurrentUser.Name & "' and RID = '" & Rid & "' and TheDate = (" & _
                      "select max(TheDate) as TheDate From Case_Notes where DataCut_ID = '" & DataCut_ID & "' and User_Name = '" & CurrentUser.Name & "' and RID = '" & Rid & "') "

                RunNonQuery(sql)


            End If
        Next


    End Sub

    Private Sub AddNote()

        'first check if you can add a note
        If TheRepeater.ItemCount <> 0 Then
            Dim TheNote As RichTextBox = TheRepeater.Controls.Item(TheRepeater.ItemCount - 1).Controls("NotesBox")

            'Dim TheUser As String

            '   TheUser = Mid(lbl.Text, 1, lbl.Text.IndexOf("-") - 2)

            '   If TheNote.Text = "" Then
            'Exit Sub
            'End If

        End If

        Dim sql As String
        Dim DataCut_ID As String
        Dim Rid As String
        Dim TheDate As String

        TheDate = Now().ToOADate - 2

        With CasesForm.TemplateGrid
            DataCut_ID = .Item(.Columns("DataCut_ID").Index, .CurrentRow.Index).Value.ToString
        End With

        With CasesForm.DetailGrid
            Rid = .Item(.Columns("RID").Index, .CurrentRow.Index).Value.ToString
        End With

        'insert a blank note
        sql = "insert into Case_Notes (DataCut_ID, RID, User_Name, TheDate, Note) values ('" & DataCut_ID & "', '" & Rid & "', '" & CurrentUser.Name & "', " & TheDate & ", '')"

        RunNonQuery(sql)
        'add a blank to the repeater
        LoadView()
        TheRepeater.AddNew()


    End Sub

    Private Sub ToolStripButton3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton3.Click
        SaveLastNote()
        AddNote()

    End Sub



    Private Sub TheRepeater_DrawItem(ByVal sender As System.Object, ByVal e As Microsoft.VisualBasic.PowerPacks.DataRepeaterItemEventArgs) Handles TheRepeater.DrawItem


        Dim rtb As RichTextBox = e.DataRepeaterItem.Controls("NotesBox")
        Dim lbl As Label = e.DataRepeaterItem.Controls("NoteLabel")

        'before you draw the last note... save it.. (if it is blank then dont bother, because clearly its not been drawn yet
        If e.DataRepeaterItem.ItemIndex = TheRepeater.ItemCount - 1 Then
            If Trim(rtb.Text) <> "" Then
                SaveLastNote()
                LoadView()
            End If

        End If

        Dim currItem As DataRowView = TheView.Item(e.DataRepeaterItem.ItemIndex)
        rtb.Text = currItem("Note")
        lbl.Text = currItem("TheDate").ToString & " - " & currItem("User_Name").ToString

        rtb.ReadOnly = True

        If e.DataRepeaterItem.ItemIndex = TheRepeater.ItemCount - 1 Then
            'check to see who owns this last note.  If it is the current user then release the read only
            If currItem("User_Name").ToString = CurrentUser.Name Then
                rtb.ReadOnly = False
            End If

        End If


    End Sub

    Private Sub CaseNotesForm_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown
        ToolStrip2.Focus()

    End Sub


    Private Sub TheRepeater_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TheRepeater.Leave
        SaveLastNote()

    End Sub

    Private Sub DeletelastNote()
        'delete the last note
        If TheRepeater.ItemCount = 0 Then Exit Sub
        'only delete it if it belongs to the current user

        Dim rpt As Microsoft.VisualBasic.PowerPacks.DataRepeaterItem

        For Each rpt In TheRepeater.Controls
            If rpt.ItemIndex = TheRepeater.ItemCount - 1 Then
                Dim TheNote As RichTextBox = rpt.Controls("NotesBox")

                '    If TheNote.Text = "" Then
                'Exit Sub
                'End If


                If TheView(TheRepeater.ItemCount - 1).Item("User_Name").ToString <> CurrentUser.Name Then Exit Sub

                'delete it
                Dim sql As String
                Dim DataCut_ID As String
                Dim Rid As String


                With CasesForm.TemplateGrid
                    DataCut_ID = .Item(.Columns("DataCut_ID").Index, .CurrentRow.Index).Value.ToString
                End With

                With CasesForm.DetailGrid
                    Rid = .Item(.Columns("RID").Index, .CurrentRow.Index).Value.ToString
                End With



                sql = "delete Case_Notes where DataCut_ID = '" & DataCut_ID & "' and User_Name = '" & CurrentUser.Name & "' and RID = '" & Rid & "' and TheDate = (" & _
                      "select max(TheDate) as TheDate From Case_Notes where DataCut_ID = '" & DataCut_ID & "' and User_Name = '" & CurrentUser.Name & "' and RID = '" & Rid & "') "

                RunNonQuery(sql)
                LoadView()
                TheRepeater.RemoveAt(rpt.ItemIndex)

            End If
        Next

    End Sub

    Private Sub ToolStripButton4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton4.Click

        DeletelastNote()

    End Sub

    Private Sub DeleteAttachements()
        If AttachmentsBox.SelectedItems.Count = 0 Then Exit Sub
        Dim sql As String
        Dim Rid As String
        Dim DataCut_ID As String

        With CasesForm.DetailGrid
            Rid = .Item(.Columns("RID").Index, .CurrentRow.Index).Value.ToString

        End With

        With CasesForm.TemplateGrid
            DataCut_ID = .Item(.Columns("DataCut_ID").Index, .CurrentRow.Index).Value.ToString
        End With

        Dim FileName As String
        With AttachmentsBox
            FileName = .SelectedItems(0).Text
        End With

        'can I delete this?

        sql = "select * from Case_Attachments where DataCut_ID = '" & DataCut_ID & "' and RID = '" & Rid & "' and FileName = '" & FileName & "'"

        Dim AView As DataView
        AView = LoadDataView(sql)

        If AView(0).Item("User_Name") <> CurrentUser.Name Then
            MessageBox.Show("You cannot delete this attachment.  It belongs to " & AView(0).Item("User_Name"), "Delete not allowed", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        If MessageBox.Show("Are you sure you want to delete this attachment?", "Delete attachment?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If



        sql = "delete from Case_Attachments where DataCut_ID = '" & DataCut_ID & "' and RID = '" & Rid & "' and FileName = '" & FileName & "'"

        RunNonQuery(sql)
        LoadAttachmentsBox()

    End Sub

    Private Sub LoadTheAttachment()

        If AttachmentsBox.SelectedItems.Count = 0 Then Exit Sub
        ShowLoadingForm()
        Me.Cursor = Cursors.WaitCursor
        Dim FileName As String
        With AttachmentsBox
            FileName = .SelectedItems(0).Text
        End With

        Dim TempFile As String
        TempFile = Environment.GetEnvironmentVariable("Temp") & "\" & FileName

        Try
            Dim strConn As String = StoreConnect
            Dim NewString As String

            NewString = strConn

            ' NewString = NewString.Substring(0, Len(NewString) - 4)
            '   NewString = NewString & "SSPI"
            Dim sql As String
            Dim Rid As String
            Dim DataCut_ID As String

            With CasesForm.DetailGrid
                Rid = .Item(.Columns("RID").Index, .CurrentRow.Index).Value.ToString

            End With

            With CasesForm.TemplateGrid
                DataCut_ID = .Item(.Columns("DataCut_ID").Index, .CurrentRow.Index).Value.ToString
            End With

            sql = "Select * from Case_Attachments where DataCut_ID = '" & DataCut_ID & "' and RID = '" & Rid & "' and FileName = '" & FileName & "'"

            Dim da As New OleDb.OleDbDataAdapter(sql, "Provider=SQLOLEDB;" & NewString)
            'Dim da As New OleDb.OleDbDataAdapter(sql, strConn)
            Dim ds As New DataSet()
            Dim cb As New OleDb.OleDbCommandBuilder(da)

            da.Fill(ds)

            Dim arr() As Byte
            arr = ds.Tables(0).Rows(0).Item("Attachment")

            FileOpen(1, TempFile, OpenMode.Binary)
            FilePut(1, arr)
            FileClose(1)
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            HideLoadingForm()
            MessageBox.Show("There has been an error, please take a screen print and send it to TheStore.EFA@education.gsi.gov.uk:  " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try


        Dim myProcess As New Process()

        Try
            myProcess.StartInfo.UseShellExecute = True
            myProcess.StartInfo.FileName = TempFile
            myProcess.Start()
            '            System.IO.Directory.SetCurrentDirectory(StoreLocalPath)
            '           System.Diagnostics.Process.Start(StoreLocalPath & "The Store.exe")
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            HideLoadingForm()
            MessageBox.Show("There has been an error, please take a screen print and send it to TheStore.EFA@education.gsi.gov.uk:  " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Me.Cursor = Cursors.Default
        End Try
        Me.Cursor = Cursors.Default
        HideLoadingForm()

    End Sub

    Private Sub AttachmentsBox_DoubleClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AttachmentsBox.DoubleClick
        LoadTheAttachment()
    End Sub

    Private Sub RemoveAttachmentButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RemoveAttachmentButton.Click
        DeleteAttachements()

    End Sub
End Class